﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;


namespace DAO
{
    class DataServices
    {
        private string connectString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        private SqlConnection connection;
        private SqlDataAdapter dataAdapter;

        public string ConnectString
        {
            get { return connectString; }
            set { connectString = value; }
        }
        public DataServices()
        {
            try
            {
                connection = new SqlConnection(connectString);
                connection.Open();
            }
            catch (Exception ex)
            {
                throw new Exception("Can't connect server " + ex.Message,ex);
            }

        }
        public DataTable executeGetDataTable(String sql, params SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }

                dataAdapter = new SqlDataAdapter(sql, connection);
                dataAdapter.SelectCommand.Parameters.AddRange(parameters);
                dataAdapter.Fill(dt);

                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error when execute get DataTable" + ex.Message,ex);
            }
            return dt;
        }
        public bool executeUpdate(String sql, params SqlParameter[] parameters)
        {
            bool result = false;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.AddRange(parameters);
                if (command.ExecuteNonQuery() > -1)
                {
                    result = true;
                }
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message,ex);
            }
            return result;
        }

        public DataTable getDateTableFormProcedure(String sql, params SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectString))
            {
                conn.Open();
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddRange(parameters);
                    SqlDataReader reader = cmd.ExecuteReader();
                    dt.Load(reader);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message,ex);
                }
                finally
                {
                    conn.Close();
                }

            }
            return dt;
        }

        public bool updateFormProcedure(String sql, params SqlParameter[] parameters)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectString))
            {
                conn.Open();
                try
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddRange(parameters);
                    int iResult = cmd.ExecuteNonQuery();
                    if (iResult > -1)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                }

            }
            return result;
        }
    }
}
